<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@page import="java.sql.*" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd">

<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <link rel="stylesheet" type="text/css" href="../css/clientTemplate.css">
        <title>Admin Template</title>
    </head>
    <body>
        <center>
        <table id="tableClientTemplate">
            <tr id="headerClientTemplate" style="background-color: orange">
                <td><%@include file="adminHeader.jsp" %></td>
            </tr>
            <tr id="middleClientTemplate">
                <td>

                    <h1>Manage Mail Box</h1><br/>
                    <%

                        try
                        {

                            Connection con = helper.ConnectionHelper.GetConnection();
                            String sql = "SELECT Users.UserName, COUNT(*) AmountNewMail "
                                    + "FROM Users, Mails "
                                    + "WHERE Users.UserName = Mails.UserName "
                                    + "AND IsAdmin = 1 AND Users.UserName != 'admin' "
                                    + "GROUP BY Users.UserName";
                            PreparedStatement p = con.prepareStatement(sql);
                            ResultSet rs = p.executeQuery();

                            while (rs.next())
                            {
                                out.print("<a href='../AdminMailBoxServlet?username=");
                                out.print(rs.getString("UserName"));
                                out.print("'>" + rs.getString("UserName") + "@srs.com</a>");
                                out.print("&nbsp;&nbsp;-&nbsp;&nbsp;");
                                out.print(rs.getInt("AmountNewMail") + " New Mail");
                                out.print("</br></br>");
                            }

                        }
                        catch (SQLException e)
                        {
                            System.out.println("SQL Exception: " + e.toString());
                        }
                    %>
                </td>
            </tr>
            <tr id="footerClientTemplate">
                <td><%@include file="adminFooter.jsp" %></td>
            </tr>
        </table>
        </center>
    </body>
</html>
